package com.demodis.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demodis.dao.DeterGroupDaoI;
import com.demodis.domain.TblDeterGroup;
import com.demodis.domain.TblUser;
import com.demodis.util.DBUtil;

public class DeterGroupDaoImpl extends BaseDao implements DeterGroupDaoI {

	@Override
	public List<TblDeterGroup> getDeterGroupsRCU(TblUser user) {
		String sql = "SELECT DGID,A.GROUPID,GROUPNAME,DETERSUBJCONT,DETERRES,DETERNUM FROM TBL_DETERGROUP A,TBL_GROUP B WHERE A.GROUPID = B.GROUPID AND A.GROUPID NOT IN(SELECT GROUPID FROM TBL_USER WHERE USERID = ?);";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getUserId());
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dealDeterGgroupsData(rs, 0);
	}
	
	@Override
	public List<TblDeterGroup> getDeterGroups() {
		String sql = "SELECT a.groupId, groupName, GROUP_CONCAT(deterSubjCont) deterSubjCont, GROUP_CONCAT(deterRes) deterStr, deterNum FROM tbl_detergroup a, tbl_group b WHERE a.groupId = b.groupId GROUP BY a.groupId;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dealDeterGgroupsData(rs, 1);
	}

	//add by robbie
	@Override
	public List<TblDeterGroup> getDeterManagerGroups(TblUser user) {
		String sql = "SELECT ROUND(SUM(score)/COUNT(*), 4) score FROM tbl_group WHERE groupLeaderId = ?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getUserId());
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dealDeterGgroupsData(rs, 1);
	}
	
	//add by robbie
		@Override
		public double getDeterManagerGroups(int userId) {
			String sql = "SELECT ROUND(SUM(score)/COUNT(*), 4) score FROM tbl_group WHERE groupLeaderId = ?";
			double score = 0;
			try {
				conn = DBUtil.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, userId);
				rs = ps.executeQuery();
				if(rs.next()) {
					score = rs.getDouble("score");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return score;
		}
	
	
	@Override
	public List<TblDeterGroup> getDeterGroupsCU(TblUser user) {
		List<TblDeterGroup> list = null;
		String sql = "SELECT a.groupId, groupName, GROUP_CONCAT(deterSubjCont) deterSubjCont, GROUP_CONCAT(deterRes) deterStr, deterNum FROM tbl_detergroup a, tbl_group b WHERE a.groupId = b.groupId AND b.groupId = ? GROUP BY a.groupId;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getGroupId());
			rs = ps.executeQuery();
			list = dealDeterGgroupsData(rs, 1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public boolean save(TblDeterGroup deterGroup) {
		String sql = "INSERT TBL_DETERGROUP(GROUPID, DETERSUBJCONT, DETERNUM) VALUES(?, ?,0);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, deterGroup.getGroupId());
			ps.setString(2, deterGroup.getDetersubjCont());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	@Override
	public boolean modify(TblDeterGroup deterGroup) {
		String sql = "UPDATE TBL_DETERGROUP SET DETERSUBJCONT = ?, GROUPID = ?, DETERRES = ? WHERE DGID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setString(1, deterGroup.getDetersubjCont());
			ps.setInt(2, deterGroup.getGroupId());
			ps.setInt(3, 0);
			ps.setInt(4, deterGroup.getDgId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public boolean delete(TblDeterGroup deterGroup) {
		String sql = "DELETE FROM TBL_DETERGROUP WHERE DGID IN(" + deterGroup.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0;
	}

	@Override
	public boolean doDeter(TblDeterGroup deterGroup) {
		String sql = "UPDATE TBL_DETERGROUP SET DETERNUM = DETERNUM + 1, DETERRES = DETERRES + ? WHERE DGID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, deterGroup.getDeterRes());
			ps.setInt(2, deterGroup.getDgId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public boolean addDeterNum() {
		String sql = "UPDATE TBL_DETERGROUP SET deterNum = deterNum + 1 WHERE DGID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);


			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}
	
	public void initTblDeterPerson() {
		String sql = "update tbl_user set detered = null;";
		String sql2 = "update tbl_detergroup set deternum = 0;";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			ps = conn.prepareStatement(sql2);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}

	private List<TblDeterGroup> dealDeterGgroupsData(ResultSet rs, int falg) {
		List<TblDeterGroup> deterList = new ArrayList<TblDeterGroup>();
		TblDeterGroup deter = null;
		try {
			while (rs.next()) {
				deter = new TblDeterGroup();
				deter.setGroupId(rs.getInt("groupId"));
				deter.setGroupName(rs.getString("groupName"));
				deter.setDetersubjCont(rs.getString("detersubjCont"));
				if(falg == 0) {
					deter.setDgId(rs.getInt("dgId"));
					deter.setDeterRes(rs.getInt("deterRes"));
				}else {
					deter.setDeterStr(rs.getString("deterStr"));
				}
				deter.setDeterNum(rs.getInt("deterNum"));
				deterList.add(deter);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return deterList;
	}

	@Override
	public double getChargeGroupResult(int userId) {
		// TODO Auto-generated method stub
		return 0;
	}

}
